IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_EntityAnnotationSaveMany]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_EntityAnnotationSaveMany]
GO
create procedure [dbo].[cms_sp_EntityAnnotationSaveMany]
@EntityId int,
@EntityTypeParameter nvarchar(400),
@ContentXml xml = null,
@AnnotationsForDelete varchar(8000) = null,
@CommentsForDelete varchar(8000) = null,
@CreatedBy int,
@DateCreated datetime,
@LastUpdatedBy int,
@DateLastUpdated datetime
as
--<?xml version="1.0" encoding="utf-16"?>
--<Annotations>
--	<Annotation>
--		<Id>0</Id>
--		<AnnotationText />
--		<X>453</X>
--		<Y>81</Y>
--		<Z>99999</Z>
--		<IsMinimized>false</IsMinimized>
--		<Comments>
--			<Comment>
--				<Id>0</Id>
--				<AnnotationId>0</AnnotationId>
--				<CommentText />
--				<OrderIndex>1</OrderIndex>
--			</Comment>
--		</Comments>
--	</Annotation>
--</Annotations>
set arithabort on
if @ContentXml is not null
begin
	declare @AnnotationId int
	declare @AnnotationText nvarchar(500)
	declare @X int
	declare @Y int
	declare @Z int
	declare @IsMinimized bit
	declare @CommentsList xml
	declare @comments table
	(
		AnnotationCommentId int,
		AnnotationId int,
		CommentText nvarchar(500),
		OrderIndex int
	)
	declare annotation_content_cursor cursor for 
	select 
		doc.col.value('(./Id)[1]', 'int'),
		doc.col.value('(./AnnotationText)[1]', 'nvarchar(500)'),
		doc.col.value('(./X)[1]', 'int'),
		doc.col.value('(./Y)[1]', 'int'),
		doc.col.value('(./Z)[1]', 'int'),
		doc.col.value('(./IsMinimized)[1]', 'bit'),
		doc.col.query('(./Comments/Comment/.)')
	from @ContentXml.nodes('/Annotations/Annotation') doc(col) 
	open annotation_content_cursor;
	fetch next from annotation_content_cursor 
	into @AnnotationId, @AnnotationText, @X, @Y, @Z, @IsMinimized, @CommentsList
	while @@fetch_status = 0
	begin
		if @AnnotationId > 0
		begin
			update dbo.Annotations
				set AnnotationText = @AnnotationText,
					X = @X,
					Y = @Y,
					Z = @Z,
					IsMinimized = @IsMinimized,
					LastUpdatedBy = @LastUpdatedBy,
					DateLastUpdated = @DateLastUpdated
			where AnnotationId = @AnnotationId
			delete from @comments
			insert into @comments
			select 
				childDoc.col.value('(./Id)[1]', 'int'),
				@AnnotationId as AnnotationId,
				childDoc.col.value('(./CommentText)[1]', 'nvarchar(500)'),
				childDoc.col.value('(./OrderIndex)[1]', 'int')
			from @CommentsList.nodes('/Comment') childDoc(col)
			insert into dbo.AnnotationComments
			select 
				tmp.AnnotationId,
				tmp.CommentText,
				tmp.OrderIndex,
				@CreatedBy,
				@DateCreated,
				null,
				null
			from @comments as tmp
			where tmp.AnnotationCommentId <= 0
			update dbo.AnnotationComments
				set CommentText = sec.CommentText
			from @comments as sec
			where AnnotationComments.AnnotationCommentId = sec.AnnotationCommentId		
		end
		else
		begin
			insert into dbo.Annotations
			(
			EntityId,
			EntityTypeParameter,
			AnnotationText,
			X,
			Y,
			Z,
			IsMinimized,
			CreatedBy,
			DateCreated
			)
			values
			(
			@EntityId,
			@EntityTypeParameter,
			@AnnotationText,
			@X,
			@Y,
			@Z,
			@IsMinimized,
			@CreatedBy,
			@DateCreated
			)
		end	
	fetch next from annotation_content_cursor 
		into @AnnotationId, @AnnotationText, @X, @Y, @Z, @IsMinimized, @CommentsList
	end
	close annotation_content_cursor;
	deallocate annotation_content_cursor;
end
delete from dbo.Annotations
where AnnotationId in
	(select tmp.integer
	from dbo.cms_udf_SplitIntegers(@AnnotationsForDelete) as tmp)
delete from dbo.AnnotationComments
where AnnotationCommentId in
	(select tmp.integer
	from dbo.cms_udf_SplitIntegers(@CommentsForDelete) as tmp)


GO

